const Router = require('koa-router');
const router = new Router();
const tool = require('../../tools/tool')
const {db, QueryTypes} = require("../../models/db");
// 销售利润表
router.post('/sale/xslrb', async ctx => {
    try {
        let body = ctx.request.body
        let tenantId = ctx.user.tenantId;
        let excel = body.excel ? 1 : 0;
        let search = body.formData;
        let beginDate = search.beginDate ? search.beginDate : '';
        let endDate = search.endDate ? search.endDate : '';
        let buId = search.salesId ? search.buId : 0;
        let salesId = search.salesId ? search.salesId : 0;
        let gpType = search.gpType ? search.gpType : '按票据';
        let goodCatCode = await tool.getGoodCode(ctx)
        let invName = search.invName?search.invName:'';
        if(!beginDate) return ctx.err('请选择开始日期')
        if(!endDate) return ctx.err('请选择结束日期')
        let currentPage = body.currentPage?body.currentPage:1;
        let pageSize = body.pageSize?body.pageSize:200;
        let offset = (currentPage-1) * pageSize;
        let sql  = ``
        let totalSql  = ``
        //
        let columns = []
        if(gpType === '按票据') {
            let wh = ''

            columns = [
                {type: 'seq', width: 50},
                {field: 'billDate', title: '日期', width: 100, align:'center',filter: true,sortable: true,visible: true},
                {field: 'buName', title: '客户名称', width: 220,filter: true,sortable: true,visible: true},
                {field: 'salesName', title: '业务员', width: 80, align:'center',sortable: true,visible: true},
                {field: 'billNo', title: '票号', width: 150, align:'center',canClick: true,sortable: true,visible: true},
                {field: 'transTypeName', title: '业务类别', width: 100, align:'center',sortable: true,visible: true},
                {field: 'totalAmount', title: '销售收入', width: 100, align:'right',sortable: true, formatter: 'formatMoney',sum: true,visible: true},
                {field: 'costMoney', title: '销售成本', width: 100, align:'right',sortable: true, formatter: 'formatMoney',sum: true,visible: true},
                {field: 'xsml', title: '销售毛利(销售收入-销售成本)', width: 100, align:'right',sortable: true, formatter: 'formatMoney',sum: true,visible: true},
                {field: 'mlv', title: '毛利率%(销售毛利/销售收)', width: 100, align:'right',sortable: true, formatter: 'formatMoney',visible: true},
                {field: 'disAmount', title: '优惠金额', width: 100, align:'right',sortable: true, formatter: 'formatMoney',sum: true,visible: true},
                {field: 'jlr', title: '销售净利润(销售毛利-优惠金额)', width: 100, align:'right',sortable: true, formatter: 'formatMoney',sum: true,visible: true},
                {field: 'jlv', title: '净利率%', width: 100, align:'right',sortable: true, formatter: 'formatMoney',visible: true},
                {field: 'yhhje', title: '优惠后金额', width: 100, align:'right',sortable: true, formatter: 'formatMoney',sum: true,visible: true},
                {field: 'amount', title: '应收金额', width: 100, align:'right',sortable: true, formatter: 'formatMoney',sum: true,visible: true},
                {field: 'hxAmount', title: '已收金额', width: 100, align:'right',sortable: true, formatter: 'formatMoney',sum: true,visible: true,},
                {field: 'remark', title: '整单备注', width: 100, align:'right',sortable: true,visible: true},
            ]
            sql = `
                select tenantId, buId, billDate, salesId, billNo, transType, transTypeName, totalAmount, disAmount, amount,remark,salesName,buName,  sum(costMoney) costMoney
                from (
                         SELECT a.tenantId,a.buId,a.billDate,a.salesId,a.billNo,a.transType,a.transTypeName,a.salesName,a.buName,
                                isnull(a.amount,0) subAmount,
                                a.totalAmountReal totalAmount,a.disAmount,a.amount,round(c.costMoney,2) costMoney,
                                a.remark,  b.invId
                         from v_main a inner join v_main_info b on a.tenantId=b.tenantId and a.id=b.iid  and (b.invName like '%${invName}%' or '${invName}' ='') and (b.goodCatCode like '${goodCatCode}%' or '${goodCatCode}' ='')
                                       inner join inventory_detail c on a.tenantId=c.tenantId and b.id = c.relId
                             and a.billState='accounted' and a.tenantId=${tenantId}
                             and a.billType='SALE' and (a.buId=${buId} or ${buId} = 0) and (a.salesId=${salesId} or ${salesId}=0)
                             and a.billDate between '${beginDate}' and '${endDate}' 
                     ) a group by tenantId, buId, billDate, salesId, billNo, transType, transTypeName,remark,salesName,buName, totalAmount, disAmount,amount 
                order by buId,billDate,salesId,transTypeName offset ${offset} row fetch next ${pageSize} row only;
            `
            totalSql = `
                select count(*) total from (
                    select tenantId, buId, billDate, salesId, billNo, transType, transTypeName, totalAmount, disAmount, amount,remark,salesName,buName,  sum(costMoney) costMoney
                from (
                         SELECT a.tenantId,a.buId,a.billDate,a.salesId,a.billNo,a.transType,a.transTypeName,a.salesName,a.buName,
                                isnull(a.amount,0) subAmount,
                                a.totalAmountReal totalAmount,a.disAmount,a.amount,round(c.costMoney,2) costMoney,
                                a.remark,  b.invId
                         from v_main a inner join v_main_info b on a.tenantId=b.tenantId and a.id=b.iid  and (b.invName like '%${invName}%' or '${invName}' ='') and (b.goodCatCode like '${goodCatCode}%' or '${goodCatCode}' ='')
                                       inner join inventory_detail c on a.tenantId=c.tenantId and b.id = c.relId
                             and a.billState='accounted' and a.tenantId=${tenantId}
                             and a.billType='SALE' and (a.buId=${buId} or ${buId} = 0) and (a.salesId=${salesId} or ${salesId}=0)
                             and a.billDate between '${beginDate}' and '${endDate}'
                     ) a group by tenantId, buId, billDate, salesId, billNo, transType, transTypeName,remark,salesName,buName, totalAmount, disAmount,amount
                              ) a 
               
            `
        }
        if(gpType === '按客户商品') {
            columns = [
                {type: 'seq', width: 50},
                {field: 'buName', title: '客户名称', width: 220,filter: true,sortable: true,visible: true},
                {field: 'invName', title: '商品名称', width: 220,filter: true,sortable: true,visible: true},
                {field: 'totalAmount', title: '销售收入', width: 100, align:'right',sortable: true, formatter: 'formatMoney',sum: true,visible: true},
                {field: 'costMoney', title: '销售成本', width: 100, align:'right',sortable: true, formatter: 'formatMoney',sum: true,visible: true},
                {field: 'xsml', title: '销售毛利(销售收入-销售成本)', width: 100, align:'right',sortable: true, formatter: 'formatMoney',visible: true},
                {field: 'mlv', title: '毛利率%(销售毛利/销售收)', width: 100, align:'right',sortable: true, formatter: 'formatMoney',visible: true},
            ]
            sql = `
             select a.tenantId,b.name invName,c.name buName, sum(a.subAmount) totalAmount,sum(costMoney) costMoney
             from (
                 select tenantId, invId,buId, sum(subAmount) subAmount, sum(costMoney) costMoney
                 from (
                      SELECT a.tenantId,a.buId,a.billDate,a.salesId,a.billNo,a.transType,a.transTypeName,
                     (isnull(a.rpAmount,0)+isnull(a.nowCheck,0)+a.yhAmount) rpAmount,
                     isnull(a.amount,0) subAmount,
                     a.totalAmountReal totalAmount,a.disAmount,a.yhAmount,a.amount,round(c.costMoney,2) costMoney,
                     a.remark,  b.invId  
                     from v_main_withhx a inner join v_main_info b on a.tenantId=b.tenantId and a.id=b.iid  and (b.invName like '%${invName}%' or '${invName}' ='') and (b.goodCatCode like '${goodCatCode}%' or '${goodCatCode}' ='')
                           inner join inventory_detail c on a.tenantId=c.tenantId and b.id = c.relId
                     and a.billState='accounted' and a.tenantId=${tenantId}
                    and a.billType='SALE' and (a.buId=${buId} or ${buId} = 0) and (a.salesId=${salesId} or ${salesId}=0)
                    and a.billDate between '${beginDate}' and '${endDate}' 
                 ) a group by tenantId,buId, invId
             ) a
            inner join v_goods  b on a.tenantId=b.tenantId and a.invId=b.id
            inner join contact  c on a.tenantId=c.tenantId and a.buId=c.id
            group by a.tenantId, b.name,c.name  order by b.name,c.name offset ${offset} row fetch next ${pageSize} row only;
            `
            totalSql = `
            select count(*) total from (
                select a.tenantId,b.name invName,c.name buName, sum(a.subAmount) totalAmount,sum(costMoney) costMoney
             from (
                 select tenantId, invId,buId, sum(subAmount) subAmount, sum(costMoney) costMoney
                 from (
                      SELECT a.tenantId,a.buId,a.billDate,a.salesId,a.billNo,a.transType,a.transTypeName,
                     (isnull(a.rpAmount,0)+isnull(a.nowCheck,0)+a.yhAmount) rpAmount,
                     isnull(a.amount,0) subAmount,
                     a.totalAmountReal totalAmount,a.disAmount,a.yhAmount,a.amount,round(c.costMoney,2) costMoney,
                     a.remark,  b.invId  
                     from v_main_withhx a inner join v_main_info b on a.tenantId=b.tenantId and a.id=b.iid  and (b.invName like '%${invName}%' or '${invName}' ='') and (b.goodCatCode like '${goodCatCode}%' or '${goodCatCode}' ='')
                           inner join inventory_detail c on a.tenantId=c.tenantId and b.id = c.relId
                     and a.billState='accounted' and a.tenantId=${tenantId}
                    and a.billType='SALE' and (a.buId=${buId} or ${buId} = 0) and (a.salesId=${salesId} or ${salesId}=0)
                    and a.billDate between '${beginDate}' and '${endDate}' 
                 ) a group by tenantId,buId, invId
             ) a
            inner join v_goods  b on a.tenantId=b.tenantId and a.invId=b.id
            inner join contact  c on a.tenantId=c.tenantId and a.buId=c.id
            group by a.tenantId, b.name,c.name  
                                       ) a 
            `
        }
        if(gpType === '按客户') {
            columns = [
                {type: 'seq', width: 50},
                {field: 'buName', title: '客户名称', width: 220,filter: true,sortable: true,visible: true},
                {field: 'totalAmount', title: '销售收入', width: 100, align:'right',sortable: true, formatter: 'formatMoney',sum: true,visible: true},
                {field: 'costMoney', title: '销售成本', width: 100, align:'right',sortable: true, formatter: 'formatMoney',sum: true,visible: true},
                {field: 'xsml', title: '销售毛利(销售收入-销售成本)', width: 100, align:'right',sortable: true, formatter: 'formatMoney',visible: true},
                {field: 'mlv', title: '毛利率%(销售毛利/销售收)', width: 100, align:'right',sortable: true, formatter: 'formatMoney',visible: true},
                {field: 'disAmount', title: '优惠金额', width: 100, align:'right',sortable: true, formatter: 'formatMoney',sum: true,visible: true},
                {field: 'jlr', title: '销售净利润(销售毛利-优惠金额)', width: 100, align:'right',sortable: true, formatter: 'formatMoney',sum: true,visible: true},
                {field: 'jlv', title: '净利率%', width: 100, align:'right',sortable: true, formatter: 'formatMoney',visible: true},
                {field: 'yhhje', title: '优惠后金额', width: 100, align:'right',sortable: true, formatter: 'formatMoney',sum: true,visible: true},
                {field: 'amount', title: '应收金额', width: 100, align:'right',sortable: true, formatter: 'formatMoney',sum: true,visible: true},
                {field: 'hxAmount', title: '已收金额', width: 100, align:'right',sortable: true, formatter: 'formatMoney',sum: true,visible: true,},
            ]
            sql = `
                select a.tenantId, b.name buName, sum(a.totalAmount) totalAmount,sum(a.disAmount) disAmount,sum(a.yhAmount) yhAmount,sum(a.amount) amount,sum(a.rpAmount) rpAmount,sum(costMoney) costMoney
                from
                (
                    select tenantId, buId,billNo,totalAmount, disAmount,yhAmount, amount,rpAmount,  sum(costMoney) costMoney
                    from(
                         SELECT a.tenantId,a.buId,a.billDate,a.salesId,a.billNo,a.transType,a.transTypeName,
                         (isnull(a.rpAmount,0)+isnull(a.nowCheck,0)+a.yhAmount) rpAmount,
                         isnull(a.amount,0) subAmount,
                         a.totalAmountReal totalAmount,a.disAmount,a.yhAmount,a.amount,round(c.costMoney,2) costMoney,
                         a.remark,  b.invId  
                         from v_main_withhx a inner join v_main_info b on a.tenantId=b.tenantId and a.id=b.iid  and (b.invName like '%${invName}%' or '${invName}' ='') and (b.goodCatCode like '${goodCatCode}%' or '${goodCatCode}' ='')
                               inner join inventory_detail c on a.tenantId=c.tenantId and b.id = c.relId
                         and a.billState='accounted' and a.tenantId=${tenantId}
                        and a.billType='SALE' and (a.buId=${buId} or ${buId} = 0) and (a.salesId=${salesId} or ${salesId}=0)
                        and a.billDate between '${beginDate}' and '${endDate}' 
                        )
                             a group by tenantId, buId, billNo,rpAmount, totalAmount, disAmount, yhAmount,amount
                )
                a inner join contact b on a.tenantId=b.tenantId and a.buId=b.id
                group by a.tenantId,  b.name  order by b.name offset ${offset} row fetch next ${pageSize} row only;
            `
            totalSql = ` select count(*) total from (
     select a.tenantId, b.name buName, sum(a.totalAmount) totalAmount,sum(a.disAmount) disAmount,sum(a.yhAmount) yhAmount,sum(a.amount) amount,sum(a.rpAmount) rpAmount,sum(costMoney) costMoney
                from
                (
                    select tenantId, buId,billNo,totalAmount, disAmount,yhAmount, amount,rpAmount,  sum(costMoney) costMoney
                    from(
                         SELECT a.tenantId,a.buId,a.billDate,a.salesId,a.billNo,a.transType,a.transTypeName,
                         (isnull(a.rpAmount,0)+isnull(a.nowCheck,0)+a.yhAmount) rpAmount,
                         isnull(a.amount,0) subAmount,
                         a.totalAmountReal totalAmount,a.disAmount,a.yhAmount,a.amount,round(c.costMoney,2) costMoney,
                         a.remark,  b.invId  
                         from v_main_withhx a inner join v_main_info b on a.tenantId=b.tenantId and a.id=b.iid  and (b.invName like '%${invName}%' or '${invName}' ='') and (b.goodCatCode like '${goodCatCode}%' or '${goodCatCode}' ='')
                               inner join inventory_detail c on a.tenantId=c.tenantId and b.id = c.relId
                         and a.billState='accounted' and a.tenantId=${tenantId}
                        and a.billType='SALE' and (a.buId=${buId} or ${buId} = 0) and (a.salesId=${salesId} or ${salesId}=0)
                        and a.billDate between '${beginDate}' and '${endDate}' 
                        )
                             a group by tenantId, buId, billNo,rpAmount, totalAmount, disAmount, yhAmount,amount
                )
                a inner join contact b on a.tenantId=b.tenantId and a.buId=b.id
                group by a.tenantId,  b.name  
                                                    ) a`
        }
        if(gpType === '按商品') {
            columns = [
                {type: 'seq', width: 50},
                {field: 'invName', title: '商品名称', width: 220,filter: true,sortable: true,visible: true},
                {field: 'totalAmount', title: '销售收入', width: 100, align:'right',sortable: true, formatter: 'formatMoney',sum: true,visible: true},
                {field: 'costMoney', title: '销售成本', width: 100, align:'right',sortable: true, formatter: 'formatMoney',sum: true,visible: true},
                {field: 'xsml', title: '销售毛利(销售收入-销售成本)', width: 100, align:'right',sortable: true, formatter: 'formatMoney',visible: true},
                {field: 'mlv', title: '毛利率%(销售毛利/销售收)', width: 100, align:'right',sortable: true, formatter: 'formatMoney',visible: true},
            ]
            sql =`
            select a.tenantId, b.name invName, sum(subAmount) totalAmount,sum(costMoney) costMoney
            from (
                     select tenantId, invId, sum(subAmount) subAmount, sum(costMoney) costMoney
                     from (
                         SELECT a.tenantId,a.buId,a.billDate,a.salesId,a.billNo,a.transType,a.transTypeName,
                         (isnull(a.rpAmount,0)+isnull(a.nowCheck,0)+a.yhAmount) rpAmount,
                         isnull(a.amount,0) subAmount,
                         a.totalAmountReal totalAmount,a.disAmount,a.yhAmount,a.amount,round(c.costMoney,2) costMoney,
                         a.remark,  b.invId  
                         from v_main_withhx a inner join v_main_info b on a.tenantId=b.tenantId and a.id=b.iid  and (b.invName like '%${invName}%' or '${invName}' ='') and (b.goodCatCode like '${goodCatCode}%' or '${goodCatCode}' ='')
                               inner join inventory_detail c on a.tenantId=c.tenantId and b.id = c.relId
                         and a.billState='accounted' and a.tenantId=${tenantId}
                        and a.billType='SALE' and (a.buId=${buId} or ${buId} = 0) and (a.salesId=${salesId} or ${salesId}=0)
                        and a.billDate between '${beginDate}' and '${endDate}' 
                    )
                              a group by tenantId, invId
            ) a inner join v_goods  b on a.tenantId=b.tenantId and a.invId=b.id  where a.tenantId=${tenantId} 
            group by a.tenantId,  b.name  order by b.name offset ${offset} row fetch next ${pageSize} row only;
            `
            totalSql = `select count(*) total from (
    select a.tenantId, b.name invName, sum(subAmount) totalAmount,sum(costMoney) costMoney
            from (
                     select tenantId, invId, sum(subAmount) subAmount, sum(costMoney) costMoney
                     from (
                         SELECT a.tenantId,a.buId,a.billDate,a.salesId,a.billNo,a.transType,a.transTypeName,
                         (isnull(a.rpAmount,0)+isnull(a.nowCheck,0)+a.yhAmount) rpAmount,
                         isnull(a.amount,0) subAmount,
                         a.totalAmountReal totalAmount,a.disAmount,a.yhAmount,a.amount,round(c.costMoney,2) costMoney,
                         a.remark,  b.invId  
                         from v_main_withhx a inner join v_main_info b on a.tenantId=b.tenantId and a.id=b.iid  and (b.invName like '%${invName}%' or '${invName}' ='') and (b.goodCatCode like '${goodCatCode}%' or '${goodCatCode}' ='')
                               inner join inventory_detail c on a.tenantId=c.tenantId and b.id = c.relId
                         and a.billState='accounted' and a.tenantId=${tenantId}
                        and a.billType='SALE' and (a.buId=${buId} or ${buId} = 0) and (a.salesId=${salesId} or ${salesId}=0)
                        and a.billDate between '${beginDate}' and '${endDate}' 
                    )
                              a group by tenantId, invId
            ) a inner join v_goods  b on a.tenantId=b.tenantId and a.invId=b.id  where a.tenantId=${tenantId} 
            group by a.tenantId,  b.name  
                                                   ) a `
        }

        columns.map(row => {
            row.excelOut = true
        })
        if(excel) {
            let list = await db.query(sql, {type: QueryTypes.SELECT})
            for (let i = 0; i < list.length; i++) {
                let row = list[i]
                list[i].costMoney = tool.xe.toFixed(row.costMoney, 2)
                list[i].xsml = tool.xe.subtract(row.totalAmount,row.costMoney)
                list[i].mlv = row.totalAmount ?tool.xe.round(tool.xe.divide(list[i].xsml, row.totalAmount)*100,2) : ''
                list[i].jlr = tool.xe.subtract(tool.xe.subtract(row.totalAmount,row.disAmount), row.costMoney)
                list[i].jlv = row.totalAmount ?tool.xe.round(tool.xe.divide(list[i].jlr, row.totalAmount)*100,2) : ''
                list[i].yhhje = tool.xe.subtract(row.totalAmount, row.disAmount)
                list[i].amount = tool.xe.subtract(tool.xe.subtract(row.totalAmount, row.disAmount), row.rpAmount)
                list[i].hxAmount = row.rpAmount
            }
            return tool.doExcelOut(ctx, list, null, columns)
        }
        else {
            let totalModel = await db.query(totalSql,{type: QueryTypes.SELECT, plain:true})
            let list = await db.query(sql , {type: QueryTypes.SELECT})
            for (let i = 0; i < list.length; i++) {
                let row = list[i]
                list[i].costMoney = tool.xe.toFixed(row.costMoney, 2)
                list[i].xsml = tool.xe.subtract(row.totalAmount,row.costMoney)
                list[i].mlv = row.totalAmount ?tool.xe.round(tool.xe.divide(list[i].xsml, row.totalAmount)*100,2) : ''
                list[i].jlr = tool.xe.subtract(tool.xe.subtract(row.totalAmount,row.disAmount), row.costMoney)
                list[i].jlv = row.totalAmount ?tool.xe.round(tool.xe.divide(list[i].jlr, row.totalAmount)*100,2) : ''
                list[i].yhhje = tool.xe.subtract(row.totalAmount, row.disAmount)
                list[i].amount = tool.xe.subtract(tool.xe.subtract(row.totalAmount, row.disAmount), row.rpAmount)
                list[i].hxAmount = row.rpAmount
            }
            return ctx.suc('', {
                total: totalModel.total,
                list,
                columns
            })
        }

    }
    catch (e) {
        return ctx.err(e)
    }

})


module.exports = router;
